from common import ReviewEntity,SearchFilter
import DatabaseConnection

class ReviewData(object):
    def __init__(self):
        pass

    def InsertReview(self,review):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('InsReview',(review.Reviewer,review.AlbumID,review.StartDate,review.SubmissionDate,\
                                     review.ReviewerComment,review.Director,review.Approved,review.ApproveDate,review.DirectorComment))
        cursor.close()
        conn.commit()
        conn.close()

    def UpdateReview(self,review):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('UpdReview',(review.Reviewer,review.AlbumID,review.StartDate,review.SubmissionDate,\
	                             review.ReviewerComment,review.Director,review.Approved,review.ApproveDate,review.DirectorComment))
        cursor.close()
        conn.commit()
        conn.close()

        pass

    def DeleteReview(self,rID,aID):
        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.callproc('DelReview',(rID,aID,))
        cursor.close()
        conn.commit()
        conn.close()

        pass

    #This method either return an object of type ReviewEntity or None (if there is no record with this ID)
    def SelectReviewByID(self,ID):
        query = """ SELECT Reviewer,AlbumID,StartDate,SubmissionDate,Director,Approved,ApproveDate,DirectorComment,AlbumName,ReviewerName,DirectorName
                    FROM vwReview
                    WHERE AlbumID ='%s'""" % ID

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        if len(result) <> 0:
            return ReviewEntity.ReviewEntity(reviewer=result[0][0],albumID=result[0][1],reviewerComment=result[0][2],director=result[0][3],\
                                             startDate=result[0][4],submissionDate=result[0][5],approved=result[0][6],approveDate=result[0][7],\
                                             directorComment=result[0][8],albumName=result[0][9],reviewerName=result[0][10],directorName=result[0][11])
        else:
            return None

    #This method returns a list of objects of type ReviewEntity
    def SelectReviewByFilter(self,searchFilter):
        query = """ SELECT Reviewer,AlbumID,StartDate,SubmissionDate,Director,Approved,ApproveDate,DirectorComment,AlbumName,ReviewerName,DirectorName
                    FROM vwReview"""

        whereClause = searchFilter.GetWhereClause()
        if whereClause.strip()<>"":
            query += " WHERE %s" % whereClause

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        recordSet = []
        for record in result:
            recordSet.append(ReviewEntity.ReviewEntity(reviewer=record[0],albumID=record[1],reviewerComment=record[2],director=record[3],\
                                                       startDate=record[4],submissionDate=record[5],approved=record[6],approveDate=record[7],\
                                                       directorCommente=record[8],albumName=record[9],reviewerName=record[0][10],directorName=result[0][11]))
        return recordSet

    def SelectAllReviews(self):
        query = """ SELECT Reviewer,AlbumID,StartDate,SubmissionDate,Director,Approved,ApproveDate,DirectorComment,AlbumName,ReviewerName,DirectorName
                    FROM vwReview"""

        conn = DatabaseConnection.GetConnection()

        cursor = conn.cursor()
        cursor.execute(query)
        result = cursor.fetchall()
        cursor.close()
        conn.close()
                
        recordSet = []
        for record in result:
            recordSet.append(ReviewEntity.ReviewEntity(reviewer=record[0],albumID=record[1],reviewerComment=record[2],director=record[3],\
                                                       startDate=record[4],submissionDate=record[5],approved=record[6],approveDate=record[7],\
                                                       directorCommente=record[8],albumName=record[9],reviewerName=record[0][10],directorName=result[0][11]))
        return recordSet
